var express = require('express')
var router = express.Router()
const { getAll, getById, exec } = require('../db/index')

/* GET users listing. */
// get /users?page=1&size=2
router.get('/', async (req, res) => {
  // 解析请求参数
  const { page = 1, size = 5 } = req.query
  let sql = `select count(*) as total from users`
  const { total } = await getById(sql)
  // 偏移量=(page-1)*size
  let offset = (page - 1) * size

  let sql1 = `select id,username from users limit ${offset},${size} `
  const data = await getAll(sql1)
  try {
    const data = await getAll(sql1)
    // console.log(data) //返回的是一个数组,
    res.send({
      code: 0,
      message: '获取所有用户成功',
      result: { total, data },
    })
  } catch (error) {
    res.send({
      code: 100101,
      message: '获取所有用户失败',
      result: debug ? error : '',
    })
  }
})

// get /users/1
router.get('/:id', async (req, res) => {
  // 解析请求参数
  const { id } = req.params
  let sql = `select * from users where id = ${id}`

  const data = await getById(sql)
  // console.log(data) //返回的是一个数组,
  res.send({
    code: 0,
    message: '获取所有用户成功',
    result: data,
  })
})

//搜索框
router.patch('/', async (req, res) => {
  // 解析请求参数
  const { username, page = 1, size = 5 } = req.query
  // console.log(req.query)
  const offset = (page - 1) * size

  // 跟句
  // let sql = `select * from users where username =${username} limit ${offset},${size}`
  // let sql1 = `select count(*) as total from users where username =${username} `

  // 模糊查询  是否加引号,传入的时候也要注意是否加了引号
  let sql = `select * from users where username like '%${username}%' limit ${offset},${size}`
  let sql1 = `select count(*) as total from users where username like '%${username}%' `

  const data = await exec(sql)
  const total = await getById(sql1)
  console.log(total)
  // console.log(data)

  try {
    // const data = await exec(sql)
    // console.log(data) //返回的是一个数组,
    res.send({
      code: 0,
      message: '获取所有用户成功',
      result: { total, data },
    })
  } catch (error) {
    res.send({
      code: 100101,
      message: '获取所有用户失败',
      result: debug ? error : '',
    })
  }
})

//post /user{username :"小明",password:'123'}

router.post('/', async (req, res) => {
  // 解析请求参数
  // id使用params参数--- username, password使用req.body错了好多次了这里
  const { username, password } = req.body
  let sql = `insert into users (username,password) values  ('${username}','${password}')`
  const { insertId } = await exec(sql)
  try {
    const data = await exec(sql)
    res.send({
      code: 0,
      message: '新增用户成功',
      result: { id: insertId, username, password },
    })
  } catch (error) {
    res.send({
      code: 100101,
      message: '新增用户失败',
      result: debug ? error : '',
    })
  }
})

//put /users/id {username:'new',password:'new'}
router.put('/:id', async function (req, res) {
  //1,获取请求的参数
  const id = req.params.id
  const { username, password } = req.body

  //2,编写sql语句
  let sql = `update users set username='${username}',password='${password}' where id = ${id}`
  console.log(sql)
  //3,执行sql语句,返回结果
  try {
    const data = await exec(sql)
    res.send({
      code: 0,
      message: '修改用户成功',
      result: {
        data,
      },
    })
  } catch (error) {
    res.send({
      code: 100107,
      message: '修改用户失败',
      result: debug ? error : '',
    })
  }
})

// 删除代办
router.delete('/:id', async function (req, res) {
  //1,获取请求的参数
  const id = req.params.id
  //2,编写sql语句
  let sql = `delete from users where id=${id}`
  //执行sql语句,返回结果
  try {
    const data = await exec(sql)
    res.send({
      code: 0,
      message: '删除用户成功',
      result: '',
    })
  } catch (error) {
    res.send({
      code: 100108,
      message: '删除用户失败',
      result: debug ? error : '',
    })
  }
})

module.exports = router
